﻿CREATE PROCEDURE [dbo].[yaf_topic_active](
                @BoardID    INT,
                @UserID     INT,
                @Since      DATETIME,
                @CategoryID INT  = NULL)
AS
    BEGIN
        SELECT   c.ForumID,
                 c.TopicID,
                 c.Posted,
                 LinkTopicID = Isnull(c.TopicMovedID,c.TopicID),
                 Subject = c.Topic,
                 c.UserID,
                 Starter = Isnull(c.UserName,b.Name),
                 Replies = (SELECT COUNT(1)
                            FROM   yaf_Message x
                            WHERE  x.TopicID = c.TopicID
                            AND (x.Flags & 8) = 0) - 1,
                 Views = c.Views,
                 LastPosted = c.LastPosted,
                 LastUserID = c.LastUserID,
                 LastUserName = Isnull(c.LastUserName,(SELECT Name
                                                       FROM   yaf_User x
                                                       WHERE  x.UserID = c.LastUserID)),
                 LastMessageID = c.LastMessageID,
                 LastTopicID = c.TopicID,
                 TopicFlags = c.Flags,
                 c.Priority,
                 c.PollID,
                 ForumName = d.Name,
                 c.TopicMovedID,
                 ForumFlags = d.Flags
        FROM     yaf_Topic c
                 JOIN yaf_User b
                   ON b.UserID = c.UserID
                 JOIN yaf_Forum d
                   ON d.ForumID = c.ForumID
                 JOIN yaf_vaccess x
                   ON x.ForumID = d.ForumID
                 JOIN yaf_Category e
                   ON e.CategoryID = d.CategoryID
        WHERE    @Since < c.LastPosted
        AND x.UserID = @UserID
        AND x.ReadAccess <> 0
        AND e.BoardID = @BoardID
        AND (c.Flags & 8) = 0
        AND (@CategoryID IS NULL 
              OR e.CategoryID = @CategoryID)
        ORDER BY d.Name ASC,
                 Priority DESC,
                 LastPosted DESC
    END


